using System;
using System.Collections.Generic;
using System.Text;
using QLKS.HeThongBUS;
using System.Data;
using System.Data.OleDb;
using QLKS.HeThongDAO;

namespace QLKS.HeThongDAO
{
    public class KH_DAO
    {   // goi table loai khach hang
        public static DataTable SelectAllLoaip()
        {
            string sql = "SELECT MaLoaiKhachHang as [Mã loại],TenLoaiKhachHang as [Tên Loại Khách Hàng] FROM LoaiKhachHang";
            return Config.AccessHelper.ExecuteQuery(sql);
        }

        // them loai khach
        public static bool Insert(KH_DTO lkh)
        {
            try
            {
                //tao cau truy van va thuc thi cau truy van
                string sql = "INSERT INTO LoaiKhachHang(TenLoaiKhachHang) VALUES ('" +
                lkh.Tenloai + "')";
                Config.AccessHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }

        // cap nhat loai khach
        public static bool Update(KH_DTO lkh)
        {
            try
            {
                string sql = "UPDATE LoaiKhachHang SET TenLoaiKhachHang = '" + lkh.Tenloai + "' where MaLoaiKhachHang=" + lkh.Maloai;
                Config.AccessHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }

        //xoa loai khach

        public static bool Delete(KH_DTO lkh)
        {
            try
            {
                string sql = "DELETE FROM LoaiKhachHang WHERE MaLoaiKhachHang=" + lkh.Maloai;
                Config.AccessHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }
        // Liet ke bang Khach hang

        public static DataTable SelectAllKH()
        {
            string sql = "SELECT MaKhachHang as [Mã Khách],MaPhong as [Phòng],TenKhachHang as [Tên Khách Hàng],CMND,DiaChi as [Địa chỉ],TenLoaiKhachHang as [Loại Khách Hàng],QuocTich as [Quốc Tịch],LoaiKhachHang.MaLoaiKhachHang FROM KhachHang,LoaiKhachHang where KhachHang.LoaiKhachHang = LoaiKhachHang.MaLoaiKhachHang";
            return Config.AccessHelper.ExecuteQuery(sql);
        }

        //Them 1 khach hang
        public static bool InsertKH(KH_DTO kh)
        {
            try
            {
                //tao cau truy van va thuc thi cau truy van
                string sql = "INSERT INTO KhachHang(MaPhong, TenKhachHang, CMND, DiaChi, LoaiKhachHang, QuocTich) VALUES ('" +
                kh.MaPhong + "','" +
                kh.TenKhach + "','" +
                kh.Cmnd + "','" +
                kh.Diachi + "','" +
                kh.Maloai + "','" +
                kh.Quoctich + "')";
                Config.AccessHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }

        // cap nhat khach hang
        public static bool UpdateKH(KH_DTO kh)
        {
            try
            {
                string sql = "UPDATE KhachHang SET MaPhong = '" + kh.MaPhong + "',TenKhachHang = '" + kh.TenKhach + "',CMND = '" + kh.Cmnd + "',DiaChi = '" + kh.Diachi + "',LoaiKhachHang = '" + kh.Maloai + "',QuocTich = '" + kh.Quoctich + "'where MaKhachHang=" + kh.MaKhach;
                Config.AccessHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }

        // xoa 1 khach hang

        public static bool DeleteKH(KH_DTO kh)
        {
            try
            {
                string sql = "DELETE FROM KhachHang WHERE MaKhachHang=" + kh.MaKhach;
                Config.AccessHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }


        // danh sach cac phong


        public static DataTable Danhsachphong()
        {
            string sql = "SELECT MaPhong FROM Phong";
            return Config.AccessHelper.ExecuteQuery(sql);
        }

        // danh sach cac loai phong


        public static DataTable Dsloaikh()
        {
            string sql = "SELECT MaLoaiKhachHang,TenLoaiKhachHang FROM LoaiKhachHang";
            return Config.AccessHelper.ExecuteQuery(sql);
        }


    }
}
